﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class OwnerTransferDetailDA : IOwnerTransferDetailDA
    {
        private string CREATED_BY = "@CREATED_BY";
        private string EXPIRY_DATE = "@EXPIRY_DATE";
        private string EXTERNAL_LOT = "@EXTERNAL_LOT";
        private string FROM_LOC = "@FROM_LOC";
        private string FROM_LOT = "@FROM_LOT";
        private string FROM_LPN_ID = "@FROM_LPN_ID";
        private string FROM_OWNER_ID = "@FROM_OWNER_ID";
        private string FROM_PACK_ID = "@FROM_PACK_ID";
        private string FROM_QTY = "@FROM_QTY";
        private string FROM_SKU_ID = "@FROM_SKU_ID";
        private string FROM_UOM_ID = "@FROM_UOM_ID";
        private string LINE_ID = "@LINE_ID";
        private string LOT_ATTR01 = "@LOT_ATTR01";
        private string LOT_ATTR02 = "@LOT_ATTR02";
        private string LOT_ATTR03 = "@LOT_ATTR03";
        private string LOT_ATTR04 = "@LOT_ATTR04";
        private string LOT_ATTR05 = "@LOT_ATTR05";
        private string LOT_ATTR06 = "@LOT_ATTR06";
        private string LOT_ATTR07 = "@LOT_ATTR07";
        private string LOT_ATTR08 = "@LOT_ATTR08";
        private string LOT_ATTR09 = "@LOT_ATTR09";
        private string LOT_ATTR10 = "@LOT_ATTR10";
        private string PRODUCE_DATE = "@PRODUCE_DATE";
        private string RECEIPT_DATE = "@RECEIPT_DATE";
        private string REMARK = "@REMARK";
        private string SQL_CHECK_TRANSFER_DETAIL_ID_UNIQUE = " SELECT COUNT(1) FROM @_@OWNER_TRANSFER_DETAIL WHERE TRANSFER_ID = @TRANSFER_ID AND LINE_ID = @LINE_ID ";
        private string SQL_CHECK_TRANSFER_DETAIL_STATUS = " UPDATE @_@OWNER_TRANSFER_DETAIL SET STATUS = @STATUS , UPDATED_BY = @UPDATED_BY,UPDATED_DATE=GETDATE()  WHERE TRANSFER_ID = @TRANSFER_ID";
        private string SQL_DELETE_ONE_TRANSFER_DETAIL = " DELETE FROM @_@OWNER_TRANSFER_DETAIL WHERE TRANSFER_ID = @TRANSFER_ID AND LINE_ID = @LINE_ID ";
        private string SQL_DELETE_TRANSFER_DETAIL = " DELETE FROM @_@OWNER_TRANSFER_DETAIL WHERE TRANSFER_ID = @TRANSFER_ID ";
        private string SQL_INSERT_TRANSFER_DETAIL = " INSERT INTO @_@OWNER_TRANSFER_DETAIL ( WH_ID, TRANSFER_ID, LINE_ID, FROM_OWNER_ID, FROM_SKU_ID, FROM_LOC, FROM_LOT, FROM_LPN_ID, FROM_QTY, FROM_PACK_ID, FROM_UOM_ID, TO_OWNER_ID, TO_SKU_ID, TO_LOC, TO_LOT, TO_LPN_ID, TO_QTY, TO_PACK_ID, TO_UOM_ID, LOT_ATTR01, LOT_ATTR02, LOT_ATTR03, LOT_ATTR04, LOT_ATTR05, LOT_ATTR06, LOT_ATTR07, LOT_ATTR08, LOT_ATTR09, LOT_ATTR10, PRODUCE_DATE, EXPIRY_DATE, RECEIPT_DATE, EXTERNAL_LOT, TRANSFER_DATE, STATUS, UDF1, UDF2, UDF3, UDF4, UDF5, REMARK, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE,VENDOR_ID) VALUES ( @WH_ID, @TRANSFER_ID, @LINE_ID, @FROM_OWNER_ID, @FROM_SKU_ID, @FROM_LOC, @FROM_LOT, @FROM_LPN_ID, @FROM_QTY, @FROM_PACK_ID, @FROM_UOM_ID, @TO_OWNER_ID, @TO_SKU_ID, @TO_LOC, @TO_LOT, @TO_LPN_ID, @TO_QTY, @TO_PACK_ID, @TO_UOM_ID, @LOT_ATTR01, @LOT_ATTR02, @LOT_ATTR03, @LOT_ATTR04, @LOT_ATTR05, @LOT_ATTR06, @LOT_ATTR07, @LOT_ATTR08, @LOT_ATTR09, @LOT_ATTR10, @PRODUCE_DATE, @EXPIRY_DATE, @RECEIPT_DATE, @EXTERNAL_LOT, @TRANSFER_DATE, @STATUS, @UDF1, @UDF2, @UDF3, @UDF4, @UDF5, @REMARK, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE(),@VENDOR_ID )  ";
        private string SQL_SELECT_ALL_TRANSFER_DETAIL = " SELECT  TD.ROWID, TD.WH_ID, TD.TRANSFER_ID, TD.LINE_ID, TD.FROM_OWNER_ID, TD.FROM_SKU_ID, TD.FROM_LOC, TD.FROM_LOT, TD.FROM_LPN_ID, TD.FROM_QTY, TD.FROM_PACK_ID, TD.FROM_UOM_ID, TD.TO_OWNER_ID, TD.TO_SKU_ID, TD.TO_LOC, TD.TO_LOT, TD.TO_LPN_ID, TD.TO_QTY, TD.TO_PACK_ID, TD.TO_UOM_ID, TD.LOT_ATTR01, TD.LOT_ATTR02, TD.LOT_ATTR03, TD.LOT_ATTR04, TD.LOT_ATTR05, TD.LOT_ATTR06, TD.LOT_ATTR07, TD.LOT_ATTR08, TD.LOT_ATTR09, TD.LOT_ATTR10, TD.PRODUCE_DATE, TD.EXPIRY_DATE, TD.RECEIPT_DATE, TD.EXTERNAL_LOT, TD.TRANSFER_DATE, TD.STATUS, TD.UDF1, TD.UDF2, TD.UDF3, TD.UDF4, TD.UDF5, TD.REMARK, TD.TS, TD.CREATED_BY, TD.CREATED_DATE, TD.UPDATED_BY, TD.UPDATED_DATE, TD.VENDOR_ID,FROM_SKU.DESCR AS FROM_SKU_DESCR,TO_SKU.DESCR AS TO_SKU_DESCR FROM  @_@OWNER_TRANSFER_DETAIL TD  LEFT JOIN @_@SKU FROM_SKU ON TD.FROM_OWNER_ID = FROM_SKU.OWNER_ID AND TD.FROM_SKU_ID = FROM_SKU.SKU_ID LEFT JOIN @_@SKU TO_SKU ON TD.TO_OWNER_ID = TO_SKU.OWNER_ID AND TD.TO_SKU_ID = TO_SKU.SKU_ID";
        private string SQL_UPDATE_TRANSFER_DETAIL = " UPDATE @_@OWNER_TRANSFER_DETAIL SET WH_ID = @WH_ID, FROM_OWNER_ID = @FROM_OWNER_ID, FROM_SKU_ID = @FROM_SKU_ID, FROM_LOC = @FROM_LOC, FROM_LOT = @FROM_LOT, FROM_LPN_ID = @FROM_LPN_ID, FROM_QTY = @FROM_QTY, FROM_PACK_ID = @FROM_PACK_ID, FROM_UOM_ID = @FROM_UOM_ID, TO_OWNER_ID = @TO_OWNER_ID, TO_SKU_ID = @TO_SKU_ID, TO_LOC = @TO_LOC, TO_LOT = @TO_LOT, TO_LPN_ID = @TO_LPN_ID, TO_QTY = @TO_QTY, TO_PACK_ID = @TO_PACK_ID, TO_UOM_ID = @TO_UOM_ID, LOT_ATTR01 = @LOT_ATTR01, LOT_ATTR02 = @LOT_ATTR02, LOT_ATTR03 = @LOT_ATTR03, LOT_ATTR04 = @LOT_ATTR04, LOT_ATTR05 = @LOT_ATTR05, LOT_ATTR06 = @LOT_ATTR06, LOT_ATTR07 = @LOT_ATTR07, LOT_ATTR08 = @LOT_ATTR08, LOT_ATTR09 = @LOT_ATTR09, LOT_ATTR10 = @LOT_ATTR10, PRODUCE_DATE = @PRODUCE_DATE, EXPIRY_DATE = @EXPIRY_DATE, RECEIPT_DATE = @RECEIPT_DATE, EXTERNAL_LOT = @EXTERNAL_LOT, TRANSFER_DATE = @TRANSFER_DATE, STATUS = @STATUS, UDF1 = @UDF1, UDF2 = @UDF2, UDF3 = @UDF3, UDF4 = @UDF4, UDF5 = @UDF5, REMARK = @REMARK, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() ,VENDOR_ID = @VENDOR_ID WHERE TRANSFER_ID = @TRANSFER_ID AND LINE_ID = @LINE_ID  ";
        private string STATUS = "@STATUS";
        private string TO_LOC = "@TO_LOC";
        private string TO_LOT = "@TO_LOT";
        private string TO_LPN_ID = "@TO_LPN_ID";
        private string TO_OWNER_ID = "@TO_OWNER_ID";
        private string TO_PACK_ID = "@TO_PACK_ID";
        private string TO_QTY = "@TO_QTY";
        private string TO_SKU_ID = "@TO_SKU_ID";
        private string TO_UOM_ID = "@TO_UOM_ID";
        private string TRANSFER_DATE = "@TRANSFER_DATE";
        private string TRANSFER_ID = "@TRANSFER_ID";
        private string TS = "@TS";
        private string UDF1 = "@UDF1";
        private string UDF2 = "@UDF2";
        private string UDF3 = "@UDF3";
        private string UDF4 = "@UDF4";
        private string UDF5 = "@UDF5";
        private string UPDATED_BY = "@UPDATED_BY";
        private string VENDOR_ID = "@VENDOR_ID";
        private string WH_ID = "@WH_ID";

        public void ChangeOwnerTransferDetailStatus(DataBase dataBase, DbTransaction tran, string transferID, string transferStatus, string updatedBy, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@STATUS", transferStatus), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@TRANSFER_ID", transferID) };
            dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_CHECK_TRANSFER_DETAIL_STATUS.Replace("@_@", whLoginID), paras);
        }

        public void ChangeOwnerTransferDetailStatus(DataBase dataBase, DbTransaction tran, string transferID, string lineID, string transferStatus, string updatedBy, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@STATUS", transferStatus), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@TRANSFER_ID", transferID), new SqlParameter("@LINE_ID", lineID) };
            dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_CHECK_TRANSFER_DETAIL_STATUS.Replace("@_@", whLoginID) + " AND LINE_ID=@LINE_ID", paras);
        }

        public bool CheckOwnerTransferDetailIDUnique(string transferID, string lineID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TRANSFER_ID", transferID), new SqlParameter("@LINE_ID", lineID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_TRANSFER_DETAIL_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckOwnerTransferDetailIDUnique(DataBase dataBase, DbTransaction tran, string transferID, string lineID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TRANSFER_ID", transferID), new SqlParameter("@LINE_ID", lineID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_TRANSFER_DETAIL_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteOwnerTransferDetail(List<string> transferID, string whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int j = 0; j < transferID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TRANSFER_ID", transferID) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_TRANSFER_DETAIL.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteOwnerTransferDetail(List<string> transferID, List<string> lineID, string whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < transferID.Count; i++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TRANSFER_ID", transferID[i]), new SqlParameter("@LINE_ID", lineID[i]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_ONE_TRANSFER_DETAIL.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteOwnerTransferDetail(DataBase dataBase, DbTransaction tran, List<string> transferID, string whLoginID)
        {
            int result = 0;
            try
            {
                for (int j = 0; j < transferID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TRANSFER_ID", transferID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_TRANSFER_DETAIL.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteOwnerTransferDetail(DataBase dataBase, DbTransaction tran, string transferID, string whLoginID)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TRANSFER_ID", transferID) };
                result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_TRANSFER_DETAIL.Replace("@_@", whLoginID), paras);
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteOwnerTransferDetail(DataBase dataBase, DbTransaction tran, List<string> transferID, List<string> lineID, string whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < transferID.Count; i++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TRANSFER_ID", transferID[i]), new SqlParameter("@LINE_ID", lineID[i]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_ONE_TRANSFER_DETAIL.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllOwnerTransferDetail(string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_TRANSFER_DETAIL.Replace("@_@", whLoginID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetAllOwnerTransferDetail(DataBase dataBase, DbTransaction tran, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_TRANSFER_DETAIL.Replace("@_@", whLoginID);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql);
        }

        public OwnerTransferDetailInfo GetOwnerTransferDetailByID(string transferID, string lineID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_TRANSFER_DETAIL.Replace("@_@", whLoginID) + " WHERE TRANSFER_ID = @TRANSFER_ID AND LINE_ID = @LINE_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TRANSFER_ID", transferID), new SqlParameter("@LINE_ID", lineID) };
            OwnerTransferDetailInfo transferDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    transferDetailInfo = new OwnerTransferDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["TRANSFER_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["FROM_OWNER_ID"].ToString(), reader["FROM_SKU_ID"].ToString(), reader["FROM_LOC"].ToString(), reader["FROM_LOT"].ToString(), reader["FROM_LPN_ID"].ToString(), (reader["FROM_QTY"].ToString() != "") ? decimal.Parse(reader["FROM_QTY"].ToString()) : 0M, reader["FROM_PACK_ID"].ToString(), reader["FROM_UOM_ID"].ToString(), reader["TO_OWNER_ID"].ToString(), reader["TO_SKU_ID"].ToString(), reader["TO_LOC"].ToString(), reader["TO_LOT"].ToString(), reader["TO_LPN_ID"].ToString(), (reader["TO_QTY"].ToString() != "") ? decimal.Parse(reader["TO_QTY"].ToString()) : 0M, reader["TO_PACK_ID"].ToString(), reader["TO_UOM_ID"].ToString(), (reader["TRANSFER_DATE"].ToString() != "") ? DateTime.Parse(reader["TRANSFER_DATE"].ToString()) : (CS00003 = new DateTime()), reader["STATUS"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["FROM_SKU_DESCR"].ToString(), reader["TO_SKU_DESCR"].ToString());
                    ReceiptLotInfo transferLotInfo = new ReceiptLotInfo(reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : new DateTime(), reader["EXTERNAL_LOT"].ToString(), reader["VENDOR_ID"].ToString());
                    transferDetailInfo.TransferLotInfo = transferLotInfo;
                }
            }
            return transferDetailInfo;
        }

        public OwnerTransferDetailInfo GetOwnerTransferDetailByID(DataBase dataBase, DbTransaction tran, string transferID, string lineID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_TRANSFER_DETAIL.Replace("@_@", whLoginID) + " WHERE TRANSFER_ID = @TRANSFER_ID AND LINE_ID = @LINE_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TRANSFER_ID", transferID), new SqlParameter("@LINE_ID", lineID) };
            OwnerTransferDetailInfo transferDetailInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                DateTime CS00003;
                transferDetailInfo = new OwnerTransferDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["TRANSFER_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["FROM_OWNER_ID"].ToString(), reader["FROM_SKU_ID"].ToString(), reader["FROM_LOC"].ToString(), reader["FROM_LOT"].ToString(), reader["FROM_LPN_ID"].ToString(), (reader["FROM_QTY"].ToString() != "") ? decimal.Parse(reader["FROM_QTY"].ToString()) : 0M, reader["FROM_PACK_ID"].ToString(), reader["FROM_UOM_ID"].ToString(), reader["TO_OWNER_ID"].ToString(), reader["TO_SKU_ID"].ToString(), reader["TO_LOC"].ToString(), reader["TO_LOT"].ToString(), reader["TO_LPN_ID"].ToString(), (reader["TO_QTY"].ToString() != "") ? decimal.Parse(reader["TO_QTY"].ToString()) : 0M, reader["TO_PACK_ID"].ToString(), reader["TO_UOM_ID"].ToString(), (reader["TRANSFER_DATE"].ToString() != "") ? DateTime.Parse(reader["TRANSFER_DATE"].ToString()) : (CS00003 = new DateTime()), reader["STATUS"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["FROM_SKU_DESCR"].ToString(), reader["TO_SKU_DESCR"].ToString());
                ReceiptLotInfo transferLotInfo = new ReceiptLotInfo(reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : new DateTime(), reader["EXTERNAL_LOT"].ToString(), reader["VENDOR_ID"].ToString());
                transferDetailInfo.TransferLotInfo = transferLotInfo;
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return transferDetailInfo;
        }

        public DataSet GetOwnerTransferDetailByQueryList(List<string> sqlWhere, OwnerTransferDetailQueryEntity transferdetailQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_TRANSFER_DETAIL.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!transferdetailQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, transferdetailQuery.CurrentPage, transferdetailQuery.PageSize, transferdetailQuery.SortField, transferdetailQuery.SortDirection);
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetOwnerTransferDetailByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, OwnerTransferDetailQueryEntity transferdetailQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_TRANSFER_DETAIL.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!transferdetailQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, transferdetailQuery.CurrentPage, transferdetailQuery.PageSize, transferdetailQuery.SortField, transferdetailQuery.SortDirection);
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public List<OwnerTransferDetailInfo> GetOwnerTransferDetailListByID(string transferID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_TRANSFER_DETAIL.Replace("@_@", whLoginID) + " WHERE TRANSFER_ID = @TRANSFER_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TRANSFER_ID", transferID) };
            List<OwnerTransferDetailInfo> transferDetailInfos = new List<OwnerTransferDetailInfo>();
            OwnerTransferDetailInfo transferDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    transferDetailInfo = new OwnerTransferDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["TRANSFER_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["FROM_OWNER_ID"].ToString(), reader["FROM_SKU_ID"].ToString(), reader["FROM_LOC"].ToString(), reader["FROM_LOT"].ToString(), reader["FROM_LPN_ID"].ToString(), (reader["FROM_QTY"].ToString() != "") ? decimal.Parse(reader["FROM_QTY"].ToString()) : 0M, reader["FROM_PACK_ID"].ToString(), reader["FROM_UOM_ID"].ToString(), reader["TO_OWNER_ID"].ToString(), reader["TO_SKU_ID"].ToString(), reader["TO_LOC"].ToString(), reader["TO_LOT"].ToString(), reader["TO_LPN_ID"].ToString(), (reader["TO_QTY"].ToString() != "") ? decimal.Parse(reader["TO_QTY"].ToString()) : 0M, reader["TO_PACK_ID"].ToString(), reader["TO_UOM_ID"].ToString(), (reader["TRANSFER_DATE"].ToString() != "") ? DateTime.Parse(reader["TRANSFER_DATE"].ToString()) : (CS00002 = new DateTime()), reader["STATUS"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["FROM_SKU_DESCR"].ToString(), reader["TO_SKU_DESCR"].ToString());
                    ReceiptLotInfo transferLotInfo = new ReceiptLotInfo(reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00002 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), reader["VENDOR_ID"].ToString());
                    transferDetailInfo.TransferLotInfo = transferLotInfo;
                    transferDetailInfos.Add(transferDetailInfo);
                }
            }
            return transferDetailInfos;
        }

        public List<OwnerTransferDetailInfo> GetOwnerTransferDetailListByID(DataBase dataBase, DbTransaction tran, string transferID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_TRANSFER_DETAIL.Replace("@_@", whLoginID) + " WHERE TRANSFER_ID = @TRANSFER_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TRANSFER_ID", transferID) };
            List<OwnerTransferDetailInfo> transferDetailInfos = new List<OwnerTransferDetailInfo>();
            OwnerTransferDetailInfo transferDetailInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            while (reader.Read())
            {
                DateTime CS00002;
                transferDetailInfo = new OwnerTransferDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["TRANSFER_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["FROM_OWNER_ID"].ToString(), reader["FROM_SKU_ID"].ToString(), reader["FROM_LOC"].ToString(), reader["FROM_LOT"].ToString(), reader["FROM_LPN_ID"].ToString(), (reader["FROM_QTY"].ToString() != "") ? decimal.Parse(reader["FROM_QTY"].ToString()) : 0M, reader["FROM_PACK_ID"].ToString(), reader["FROM_UOM_ID"].ToString(), reader["TO_OWNER_ID"].ToString(), reader["TO_SKU_ID"].ToString(), reader["TO_LOC"].ToString(), reader["TO_LOT"].ToString(), reader["TO_LPN_ID"].ToString(), (reader["TO_QTY"].ToString() != "") ? decimal.Parse(reader["TO_QTY"].ToString()) : 0M, reader["TO_PACK_ID"].ToString(), reader["TO_UOM_ID"].ToString(), (reader["TRANSFER_DATE"].ToString() != "") ? DateTime.Parse(reader["TRANSFER_DATE"].ToString()) : (CS00002 = new DateTime()), reader["STATUS"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["FROM_SKU_DESCR"].ToString(), reader["TO_SKU_DESCR"].ToString());
                ReceiptLotInfo transferLotInfo = new ReceiptLotInfo(reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00002 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), reader["VENDOR_ID"].ToString());
                transferDetailInfo.TransferLotInfo = transferLotInfo;
                transferDetailInfos.Add(transferDetailInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return transferDetailInfos;
        }

        public List<OwnerTransferDetailInfo> GetOwnerTransferDetailListByStatus(string transferID, string WhereStatus, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_TRANSFER_DETAIL.Replace("@_@", whLoginID) + " WHERE TRANSFER_ID = @TRANSFER_ID" + WhereStatus;
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TRANSFER_ID", transferID) };
            List<OwnerTransferDetailInfo> transferDetailInfos = new List<OwnerTransferDetailInfo>();
            OwnerTransferDetailInfo transferDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    transferDetailInfo = new OwnerTransferDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["TRANSFER_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["FROM_OWNER_ID"].ToString(), reader["FROM_SKU_ID"].ToString(), reader["FROM_LOC"].ToString(), reader["FROM_LOT"].ToString(), reader["FROM_LPN_ID"].ToString(), (reader["FROM_QTY"].ToString() != "") ? decimal.Parse(reader["FROM_QTY"].ToString()) : 0M, reader["FROM_PACK_ID"].ToString(), reader["FROM_UOM_ID"].ToString(), reader["TO_OWNER_ID"].ToString(), reader["TO_SKU_ID"].ToString(), reader["TO_LOC"].ToString(), reader["TO_LOT"].ToString(), reader["TO_LPN_ID"].ToString(), (reader["TO_QTY"].ToString() != "") ? decimal.Parse(reader["TO_QTY"].ToString()) : 0M, reader["TO_PACK_ID"].ToString(), reader["TO_UOM_ID"].ToString(), (reader["TRANSFER_DATE"].ToString() != "") ? DateTime.Parse(reader["TRANSFER_DATE"].ToString()) : (CS00002 = new DateTime()), reader["STATUS"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["FROM_SKU_DESCR"].ToString(), reader["TO_SKU_DESCR"].ToString());
                    ReceiptLotInfo transferLotInfo = new ReceiptLotInfo(reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00002 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), reader["VENDOR_ID"].ToString());
                    transferDetailInfo.TransferLotInfo = transferLotInfo;
                    transferDetailInfos.Add(transferDetailInfo);
                }
            }
            return transferDetailInfos;
        }

        public List<OwnerTransferDetailInfo> GetOwnerTransferDetailListByStatus(DataBase dataBase, DbTransaction tran, string transferID, string WhereStatus, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_TRANSFER_DETAIL.Replace("@_@", whLoginID) + " WHERE TRANSFER_ID = @TRANSFER_ID " + WhereStatus;
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TRANSFER_ID", transferID) };
            List<OwnerTransferDetailInfo> transferDetailInfos = new List<OwnerTransferDetailInfo>();
            OwnerTransferDetailInfo transferDetailInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            while (reader.Read())
            {
                DateTime CS00002;
                transferDetailInfo = new OwnerTransferDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["TRANSFER_ID"].ToString(), (reader["LINE_ID"].ToString() != "") ? int.Parse(reader["LINE_ID"].ToString()) : 0, reader["FROM_OWNER_ID"].ToString(), reader["FROM_SKU_ID"].ToString(), reader["FROM_LOC"].ToString(), reader["FROM_LOT"].ToString(), reader["FROM_LPN_ID"].ToString(), (reader["FROM_QTY"].ToString() != "") ? decimal.Parse(reader["FROM_QTY"].ToString()) : 0M, reader["FROM_PACK_ID"].ToString(), reader["FROM_UOM_ID"].ToString(), reader["TO_OWNER_ID"].ToString(), reader["TO_SKU_ID"].ToString(), reader["TO_LOC"].ToString(), reader["TO_LOT"].ToString(), reader["TO_LPN_ID"].ToString(), (reader["TO_QTY"].ToString() != "") ? decimal.Parse(reader["TO_QTY"].ToString()) : 0M, reader["TO_PACK_ID"].ToString(), reader["TO_UOM_ID"].ToString(), (reader["TRANSFER_DATE"].ToString() != "") ? DateTime.Parse(reader["TRANSFER_DATE"].ToString()) : (CS00002 = new DateTime()), reader["STATUS"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["FROM_SKU_DESCR"].ToString(), reader["TO_SKU_DESCR"].ToString());
                ReceiptLotInfo transferLotInfo = new ReceiptLotInfo(reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00002 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), reader["VENDOR_ID"].ToString());
                transferDetailInfo.TransferLotInfo = transferLotInfo;
                transferDetailInfos.Add(transferDetailInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return transferDetailInfos;
        }

        public int InsertOwnerTransferDetail(OwnerTransferDetailInfo transferDetailInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                transferDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_OwnerTransferDetail_Parameters(transferDetailInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_TRANSFER_DETAIL.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int InsertOwnerTransferDetail(DataBase dataBase, DbTransaction tran, OwnerTransferDetailInfo transferDetailInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                transferDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_OwnerTransferDetail_Parameters(transferDetailInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_TRANSFER_DETAIL.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_OwnerTransferDetail_Parameters(OwnerTransferDetailInfo transferDetailInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.TRANSFER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.LINE_ID, SqlDbType.Int, 4), new SqlParameter(this.FROM_OWNER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.FROM_SKU_ID, SqlDbType.VarChar, 50), new SqlParameter(this.FROM_LOC, SqlDbType.VarChar, 20), new SqlParameter(this.FROM_LOT, SqlDbType.VarChar, 20), new SqlParameter(this.FROM_LPN_ID, SqlDbType.VarChar, 20), new SqlParameter(this.FROM_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.FROM_PACK_ID, SqlDbType.VarChar, 50), new SqlParameter(this.FROM_UOM_ID, SqlDbType.VarChar, 20), new SqlParameter(this.TO_OWNER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.TO_SKU_ID, SqlDbType.VarChar, 50), new SqlParameter(this.TO_LOC, SqlDbType.VarChar, 20), new SqlParameter(this.TO_LOT, SqlDbType.VarChar, 20), new SqlParameter(this.TO_LPN_ID, SqlDbType.VarChar, 20), 
                new SqlParameter(this.TO_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.TO_PACK_ID, SqlDbType.VarChar, 50), new SqlParameter(this.TO_UOM_ID, SqlDbType.VarChar, 20), new SqlParameter(this.LOT_ATTR01, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR02, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR03, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR04, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR05, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR06, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR07, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR08, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR09, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR10, SqlDbType.VarChar, 50), new SqlParameter(this.PRODUCE_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.EXPIRY_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.RECEIPT_DATE, SqlDbType.DateTime, 8), 
                new SqlParameter(this.EXTERNAL_LOT, SqlDbType.VarChar, 100), new SqlParameter(this.TRANSFER_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.STATUS, SqlDbType.VarChar, 20), new SqlParameter(this.UDF1, SqlDbType.VarChar, 30), new SqlParameter(this.UDF2, SqlDbType.VarChar, 30), new SqlParameter(this.UDF3, SqlDbType.VarChar, 30), new SqlParameter(this.UDF4, SqlDbType.VarChar, 30), new SqlParameter(this.UDF5, SqlDbType.VarChar, 30), new SqlParameter(this.REMARK, SqlDbType.VarChar, 0x3e8), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.VENDOR_ID, SqlDbType.VarChar, 20)
             };
            if (!string.IsNullOrEmpty(transferDetailInfo.WhID))
            {
                paramArray[0].Value = transferDetailInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = transferDetailInfo.TransferID;
            paramArray[2].Value = transferDetailInfo.LineID;
            paramArray[3].Value = transferDetailInfo.FromOwnerID;
            paramArray[4].Value = transferDetailInfo.FromSkuID;
            paramArray[5].Value = transferDetailInfo.FromLoc;
            paramArray[6].Value = transferDetailInfo.FromLot;
            paramArray[7].Value = transferDetailInfo.FromLpnID;
            paramArray[8].Value = transferDetailInfo.FromQty;
            paramArray[9].Value = transferDetailInfo.FromPackID;
            paramArray[10].Value = transferDetailInfo.FromUomID;
            paramArray[11].Value = transferDetailInfo.ToOwnerID;
            paramArray[12].Value = transferDetailInfo.ToSkuID;
            paramArray[13].Value = transferDetailInfo.ToLoc;
            paramArray[14].Value = transferDetailInfo.ToLot;
            paramArray[15].Value = transferDetailInfo.ToLpnID;
            paramArray[0x10].Value = transferDetailInfo.ToQty;
            paramArray[0x11].Value = transferDetailInfo.ToPackID;
            paramArray[0x12].Value = transferDetailInfo.ToUomID;
            paramArray[0x13].Value = transferDetailInfo.TransferLotInfo.LotAttr01;
            paramArray[20].Value = transferDetailInfo.TransferLotInfo.LotAttr02;
            paramArray[0x15].Value = transferDetailInfo.TransferLotInfo.LotAttr03;
            paramArray[0x16].Value = transferDetailInfo.TransferLotInfo.LotAttr04;
            paramArray[0x17].Value = transferDetailInfo.TransferLotInfo.LotAttr05;
            paramArray[0x18].Value = transferDetailInfo.TransferLotInfo.LotAttr06;
            paramArray[0x19].Value = transferDetailInfo.TransferLotInfo.LotAttr07;
            paramArray[0x1a].Value = transferDetailInfo.TransferLotInfo.LotAttr08;
            paramArray[0x1b].Value = transferDetailInfo.TransferLotInfo.LotAttr09;
            paramArray[0x1c].Value = transferDetailInfo.TransferLotInfo.LotAttr10;
            DateTime CS00003 = new DateTime();
            if (transferDetailInfo.TransferLotInfo.ProduceDate != CS00003)
            {
                paramArray[0x1d].Value = transferDetailInfo.TransferLotInfo.ProduceDate;
            }
            else
            {
                paramArray[0x1d].Value = DBNull.Value;
            }
            CS00003 = new DateTime();
            if (transferDetailInfo.TransferLotInfo.ExpiryDate != CS00003)
            {
                paramArray[30].Value = transferDetailInfo.TransferLotInfo.ExpiryDate;
            }
            else
            {
                paramArray[30].Value = DBNull.Value;
            }
            if (transferDetailInfo.TransferLotInfo.ReceiptDate != new DateTime())
            {
                paramArray[0x1f].Value = transferDetailInfo.TransferLotInfo.ReceiptDate;
            }
            else
            {
                paramArray[0x1f].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(transferDetailInfo.TransferLotInfo.ExternalLot))
            {
                paramArray[0x20].Value = transferDetailInfo.TransferLotInfo.ExternalLot;
            }
            else
            {
                paramArray[0x20].Value = DBNull.Value;
            }
            paramArray[0x21].Value = transferDetailInfo.TransferDate;
            paramArray[0x22].Value = transferDetailInfo.Status;
            if (!string.IsNullOrEmpty(transferDetailInfo.Udf1))
            {
                paramArray[0x23].Value = transferDetailInfo.Udf1;
            }
            else
            {
                paramArray[0x23].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(transferDetailInfo.Udf2))
            {
                paramArray[0x24].Value = transferDetailInfo.Udf2;
            }
            else
            {
                paramArray[0x24].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(transferDetailInfo.Udf3))
            {
                paramArray[0x25].Value = transferDetailInfo.Udf3;
            }
            else
            {
                paramArray[0x25].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(transferDetailInfo.Udf4))
            {
                paramArray[0x26].Value = transferDetailInfo.Udf4;
            }
            else
            {
                paramArray[0x26].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(transferDetailInfo.Udf5))
            {
                paramArray[0x27].Value = transferDetailInfo.Udf5;
            }
            else
            {
                paramArray[0x27].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(transferDetailInfo.Remark))
            {
                paramArray[40].Value = transferDetailInfo.Remark;
            }
            else
            {
                paramArray[40].Value = DBNull.Value;
            }
            paramArray[0x29].Value = transferDetailInfo.CreatedBy;
            paramArray[0x2a].Value = transferDetailInfo.UpdatedBy;
            paramArray[0x2b].Value = transferDetailInfo.TransferLotInfo.VendorID;
            return paramArray;
        }

        public int UpdateOwnerTransferDetail(OwnerTransferDetailInfo transferDetailInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                transferDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_OwnerTransferDetail_Parameters(transferDetailInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_TRANSFER_DETAIL.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateOwnerTransferDetail(DataBase dataBase, DbTransaction tran, OwnerTransferDetailInfo transferDetailInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                transferDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_OwnerTransferDetail_Parameters(transferDetailInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_TRANSFER_DETAIL.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

